package pe.gob.foncodes.ssa.bs.common.dao.impl;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.Query;

import oracle.jdbc.OracleTypes;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.springframework.orm.jpa.support.JpaDaoSupport;

import pe.gob.foncodes.ssa.bs.common.dao.GenericDao;
import pe.gob.foncodes.ssa.bs.common.util.DaoException;

public class GenericDaoJpa extends JpaDaoSupport implements GenericDao {

    public static final int RESULT_VOID = 2;
    public static final int RESULT_LIST = 1;
    public static final int RESULT_MAP = 3;
    public static final int RESULT_OBJECT = 0;
    protected final Logger log = Logger.getLogger(getClass());
    private Map fieldSearchConfig;
    private MatchMode matchMode = MatchMode.ANYWHERE;

    public GenericDaoJpa() {
    }

    @Override
    public Map getFieldSearchConfig() {
        return fieldSearchConfig;
    }

    @Override
    public MatchMode getMatchMode() {
        return matchMode;
    }

    @Override
    public void setMatchMode(MatchMode matchMode) {
        this.matchMode = matchMode;
    }

    @Override
    public void setFieldSearchConfig(Map fieldSearchConfig) {
        this.fieldSearchConfig = fieldSearchConfig;
    }

    @Override
    public Connection getConnection() throws DaoException {
        Connection cnx = null;
        Session hibernateSession = null;
        String driver = null;
        try {
            hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
            cnx = hibernateSession.getSessionFactory().openStatelessSession().connection();
        } catch (Exception e) {
            log.error(e);
            throw new DaoException(e);
        }
        return cnx;
    }

    @Override
    public Session getSession() throws DaoException {
        Connection cnx = null;
        Session session = null;
        Session hibernateSession = null;
        String driver = null;
        try {
            hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
            session = hibernateSession.getSessionFactory().openSession();
        } catch (Exception e) {
            log.error(e);
            throw new DaoException(e);
        }
        return session;
    }

    @Override
    public void delete(Object persistentInstance) throws DaoException {
        log.debug("deleting instance");
        try {
            getJpaTemplate().remove(getJpaTemplate().contains(persistentInstance) ? persistentInstance : getJpaTemplate().merge(persistentInstance));
            //getJpaTemplate().refresh(persistentInstance);
            //getJpaTemplate().remove(persistentInstance);
            log.debug("delete successful");
        } catch (RuntimeException e) {
            log.error("delete failed", e);
            throw new DaoException(e);
        } catch (Exception e) {
            log.error("delete failed", e);
            throw new DaoException(e);
        }
    }

    @Override
    public void delete(Collection collection) throws DaoException {
        for (Iterator it = collection.iterator(); it.hasNext(); delete(it.next()));
    }

    public Criteria getCriteria(Class clase) {
        Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
        return hibernateSession.createCriteria(clase);
    }

    @Override
    public void deleteById(Class clazz, Serializable id) throws DaoException {
        log.debug("deleting instance with id: " + id);
        try {
            Object object = findById(clazz, id);
            getJpaTemplate().remove(object);
        } catch (DaoException e) {
            log.error(e);
            throw new DaoException(e);
        }
    }

    @Override
    public Object getReference(Class clazz, Serializable id) throws DaoException {
        log.debug("get reference to instance:" + id);
        Object result = null;
        try {
            result = getJpaTemplate().getReference(clazz, id);
        } catch (Exception e) {
            log.error(e);
            throw new DaoException(e);
        }
        return result;
    }

    @Override
    public boolean exists(Class clazz, String id) throws DaoException {
        return false;
    }

    @Override
    public Object findById(Class clazz, Serializable id) throws DaoException {
        try {
            log.debug("find instance with id: " + id.toString());
            Object instance = getJpaTemplate().find(clazz, id);
            return instance;

        } catch (Exception e) {
            throw new DaoException(e);
        }
    }

    @Override
    public Object findByCode(Class clazz, String code) throws DaoException {
        Object instance = null;
        try {
            log.debug("find instance with Code: " + code.toString());
            List l = getJpaTemplate().find("select usr.id from " + clazz.getName() + " usr where usr.codUsuario='" + code + "'");
            if (l != null && l.size() == 1) {
                Long s = (Long) l.get(0);
                instance = getJpaTemplate().find(clazz, s);
            }
            return instance;

        } catch (Exception e) {
            throw new DaoException(e);
        }
    }

    @Override
    public Object findGeneralByCode(Class clazz, String code) throws DaoException {
        Object instance = null;
        try {
            List l = getJpaTemplate().find("select g.id from " + clazz.getName() + " g where g.txtParametro='" + code + "' AND g.flgActivo = '1'");
            if (l != null && l.size() == 1) {
                Long s = (Long) l.get(0);
                instance = getJpaTemplate().find(clazz, s);
            }
            return instance;
        } catch (Exception e) {
            throw new DaoException(e);
        }
    }

    @Override
    public List findByObject(Object object) throws DaoException {
        List list = new ArrayList();
        try {
            log.debug("find instance by object: " + object.toString());
            Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
            Example example = Example.create(object);
            list = hibernateSession.createCriteria(object.getClass()).add(
                    example).list();
            return list;
        } catch (Exception e) {
            throw new DaoException(e);
        }
    }

    @Override
    public Date getCurrentDate() throws DaoException {
        Session hibernateSession = null;
        String driver = null;
        try {
            hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
            driver = hibernateSession.getSessionFactory().openStatelessSession().connection().getMetaData().getDriverName();
        } catch (Exception e) {
            throw new DaoException(e);
        }
        if (!StringUtils.isEmpty(driver)) {
            if (driver.indexOf("Oracle") != -1) {
                SQLQuery query = hibernateSession.getSessionFactory().getCurrentSession().createSQLQuery(
                        "SELECT SYSDATE FROM DUAL");
                return (Date) query.uniqueResult();
            }
            if (driver.indexOf("MySql") != -1) {
                SQLQuery query = hibernateSession.getSessionFactory().getCurrentSession().createSQLQuery("SELECT CURDATE()");
                return (Date) query.uniqueResult();
            }
        }
        return null;
    }

    @Override
    public String getIdFieldName(Class clazz) throws DaoException {
        Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
        return hibernateSession.getSessionFactory().getClassMetadata(clazz).getIdentifierPropertyName();
    }

    @Override
    public void initialize(Object object) throws DaoException {
        Hibernate.initialize(object);
    }

    @Override
    public void initializeCollection(Collection collection) throws DaoException {
        for (Iterator it = collection.iterator(); it.hasNext(); initialize(it.next()));
    }

    @Override
    public void persist(Object object) throws DaoException {
        log.debug("persisting instance");
        try {
            getJpaTemplate().persist(object);
            log.debug("save successful");
        } catch (Exception re) {
            log.error("save failed", re);
            throw new DaoException(re);
        }
    }

    @Override
    public Object save(Object object) throws DaoException {
        log.debug("saving instance");
        Object result = null;
        try {
            result = getJpaTemplate().merge(object);
            log.debug("save successful");
        } catch (Exception re) {
            log.error("save failed", re);
            StackTraceElement[] xx = re.getStackTrace();
            System.err.println("ERROR: " + re.getMessage());
            for (int i = 0; i < xx.length; i++) {
                System.err.println(xx[i]);
            }
            throw new DaoException(re);
        }
        return result;
    }

    @Override
    public void saveCollection(Collection collection) throws DaoException {
        for (Iterator it = collection.iterator(); it.hasNext();
                save(it.next()));
    }

    @Override
    public void refresh(Object object) throws DaoException {
        Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
        //Session hibernateSession = (Session)getJpaTemplate().getEntityManager().getDelegate();
        hibernateSession.refresh(object);
    }

    @Override
    public List findByNamedQuery(String queryName) throws DaoException {
        return getJpaTemplate().findByNamedQuery(queryName);
    }

    @Override
    public List findByNamedQuery(String queryName, Object[] values) throws DaoException {
        return getJpaTemplate().findByNamedQuery(queryName, values);
    }

    @Override
    public Object findByNamedQueryUniqueRow(String queryName, Object[] values) throws DaoException {
        Object result = null;
        List<Object> tempList = getJpaTemplate().findByNamedQuery(queryName, values);
        if (!tempList.isEmpty()) {
            result = tempList.get(0);
        }
        return result;
    }

    @Override
    public List findByNamedQuery(String queryName, Map<String, ?> params) throws DaoException {
        return getJpaTemplate().findByNamedQueryAndNamedParams(queryName, params);
    }

    @Override
    public Integer executeUpdate(String queryName, Object[] values) throws DaoException {
        EntityManager entityManager = getJpaTemplate().getEntityManagerFactory().createEntityManager();
        EntityTransaction entityTransaction = entityManager.getTransaction();
        entityTransaction.begin();
        //Query query = entityManager.createNamedQuery(queryName);
        Query query = entityManager.createNamedQuery(queryName);
        for (int i = 0; i < values.length; i++) {
            query.setParameter(i + 1, values[i]);
        }
        Integer result = query.executeUpdate();
        entityTransaction.commit();
        entityManager.close();
        return result;
    }

    @Override
    public void flush() throws DaoException {
        try {
            getJpaTemplate().flush();
        } catch (Exception e) {
            throw new DaoException(e);
        }
    }

    @Override
    public List createNativeQuery(String queryName, Class clase) throws DaoException {
        Query query = getJpaTemplate().getEntityManager().createNativeQuery(queryName, clase);
        return query.getResultList();
    }

    @Override
    public List createNativeQuery(String queryName, Object[] values) throws DaoException {
        Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
        SQLQuery sqlQuery = hibernateSession.createSQLQuery(queryName);
        return sqlQuery.list();
    }

    @Override
    public Object createNativeQuery(String queryName) throws DaoException {
        Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
        SQLQuery sqlQuery = hibernateSession.createSQLQuery(queryName);
        return sqlQuery.uniqueResult();
    }

    @Override
    public List find(String queryName) throws DaoException {
        return getJpaTemplate().find(queryName);
    }

    @Override
    public List findByObjectDif(String clase, String campo, String valor, Long idObj) throws DaoException {
        List list = new ArrayList();
        try {
            Session hibernateSession = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();
            list = hibernateSession.createQuery("select c." + campo + " from " + clase + " c where LOWER(c." + campo + ")='" + valor.toLowerCase() + "' and c.id !=" + idObj).list();
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public boolean actualizaEnCascada(Long id, String flgActivo, String nombreProcedure) throws DaoException, Exception {
        Session ses = getSession();
        Connection con = ses.connection();
        CallableStatement cstmt = null;
        try {
            cstmt = con.prepareCall("BEGIN SSA.PQ_ACTUALIZACIONESTADOS." + nombreProcedure + "(?, ?); END;");
            cstmt.setLong(1, id);
            cstmt.setInt(2, Integer.parseInt(flgActivo));
            cstmt.execute();
        } catch (Exception e) {
            log.error("Error de BD, verificar: ", e);
        } finally {
            try {
                DbUtils.close(cstmt);
            } finally {
                DbUtils.close(con);
                ses.close();
            }
        }
        return true;
    }

    @Override
    public boolean agregaEnCascada(Long idOpcion, Long idAccion, Long idCtrlWeb, String flgActivo, String nombreProcedure) throws DaoException, Exception {
        Session ses = getSession();
        Connection con = ses.connection();
        CallableStatement cstmt = null;
        try {
            cstmt = con.prepareCall("BEGIN SSA.PQ_ACTUALIZACIONESTADOS." + nombreProcedure + "(?, ?, ? , ?); END;");
            cstmt.setLong(1, idOpcion);
            cstmt.setLong(2, idAccion);
            cstmt.setInt(3, Integer.parseInt(flgActivo));
            cstmt.setLong(4, idCtrlWeb);
            cstmt.execute();
        } catch (Exception e) {
            log.error("Error de BD, verificar: ", e);
        } finally {
            try {
                DbUtils.close(cstmt);
            } finally {
                DbUtils.close(con);
                ses.close();
            }
        }
        return true;
    }

    @Override
    public List<Long> getModulosHijos(Long nidModulo) throws DaoException {
        String sql = "";
        sql = "SELECT NID_MODULO FROM ssa.modulo ";
        sql += "START WITH NID_MODULO = " + nidModulo;
        sql += " CONNECT BY PRIOR NID_MODULO = NID_MODULO_PADRE";

        List<Long> lstModulos = new ArrayList<Long>();
        List<Object> lista = createNativeQuery(sql, new Object[]{nidModulo});
        for (Object obj : lista) {
            if (obj != null) {
                lstModulos.add(Long.parseLong(obj.toString()));
            }
        }
        return lstModulos;
    }

    @Override
    public boolean validaExistenciaEnPerfilOAutorizacion(Long nid, Long nidOpcion, String sTipo) throws DaoException {
        String jpql = "";
        Object[] opciones = null;
        if (sTipo.equals("1")) {
            //opcion
            if (nidOpcion == 0) {
                opciones = new Object[]{nid};
                jpql = "UsuarioAutorizacion.findByNiOpcionINdUsuarioPerfil";
            } else {
                opciones = new Object[]{nid, nidOpcion};
                jpql = "UsuarioAutorizacion.findByNiOpcionAndPadreINdUsuarioPerfil";
            }
        } else if (sTipo.equals("2")) {
            //accion
            jpql = "UsuarioAutorizacion.findByNidAccionINUsuarioPerfil";
            opciones = new Object[]{nid, nidOpcion};
        } else if (sTipo.equals("3")) {
            jpql = "UsuarioPerfil.findByNidPerfil";
            opciones = new Object[]{nid};
        }
        try {
            List<Object> lista = findByNamedQuery(jpql, opciones);
            if (!lista.isEmpty()) {
                return true;
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }

    @Override
    public List<Object[]> reporteOpcionxModulo(Long nidModulo, Long nidPerfil, String codUsuario) throws DaoException, Exception {
        Session ses = getSession();
        Connection con = ses.connection();
        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<Object[]> lstResult = new ArrayList<Object[]>();
        Object[] result = null;
        try {
            cstmt = con.prepareCall("{call SSA.PQ_ACTUALIZACIONESTADOS.SP_OPCIONXMODULO(?, ?, ?, ?)}");
            cstmt.setLong(1, nidModulo);
            cstmt.setLong(2, nidPerfil);
            cstmt.setString(3, codUsuario);
            cstmt.registerOutParameter(4, OracleTypes.CURSOR);

            cstmt.execute();

            rs = (ResultSet) cstmt.getObject(4);
            int numcols = rs.getMetaData().getColumnCount();
            while(rs.next()){
                result = new Object[numcols];                    
                for (int i = 1; i <= numcols; i++) {  // don't skip the last column, use <=
                    result[i-1] = rs.getObject(i);                    
                }
                lstResult.add(result);
            }

        } catch (Exception e) {
            log.error("Error de BD, verificar: ", e);
        } finally {
            try {
                DbUtils.close(cstmt);
            } finally {
                DbUtils.close(con);
                ses.close();
            }
        }
        return lstResult;
    }
    
    @Override
    public boolean actualizaPerfilOpcionEnCascada(Long idPerfil, Long idOpcion, String flgConsulta, String flgActivo, Long idCtrlWeb, String nombreProcedure) throws DaoException, Exception {
        Session ses = getSession();
        Connection con = ses.connection();
        CallableStatement cstmt = null;
        try {
            cstmt = con.prepareCall("BEGIN SSA.PQ_ACTUALIZACIONESTADOS."+ nombreProcedure +"(?, ?, ?, ? , ?); END;");
            cstmt.setLong(1, idPerfil);
            cstmt.setLong(2, idOpcion);
            cstmt.setString(3, flgConsulta);
            cstmt.setInt(4, Integer.parseInt(flgActivo));
            cstmt.setLong(5, idCtrlWeb);
            cstmt.execute();
        } catch (Exception e) {
            log.error("Error de BD, verificar: ", e);
        } finally {
            try {
                DbUtils.close(cstmt);
            } finally {
                DbUtils.close(con);
                ses.close();
            }
        }
        return true;
    }
}